<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

namespace Home\Controller;
use Home\Controller\CommonController;

/**
 * Description of ExeclController
 *
 * @author Mr.Deng
 */
class ExeclController extends CommonController{
    public function __construct() {
        parent::__construct();
       vendor("PHPExcel.PHPExcel");
    }
    //批量生成账号
    public function exportUser(){
         $admin_user = D('AdminUser');
         $id = $admin_user->lastId();
         $idnum = intval($id['user_id'])+1;         //dump($idnum);exit;
         $data = [];
         for($i=0;$i<5;$i++){
             $username = 'yun'.mt_rand(1, 1000).$idnum;
             $result['user_name'] = $username;
             $result['password2'] = (string)mt_rand();
             $result['ec_salt'] = '3241';
             $result['password'] = md5(md5($result['password2']).$result['ec_salt']);
             $result['add_time'] = time();
             $result['last_login'] = time();
             $result['role_id'] = (int)2;
             $data[] = $result;
             $idnum++;
         }
//         dump($data);exit;
         $insert = $admin_user->addAll($data);
         if(!$insert){
             $this->error($admin_user->getDbError());
         }
         
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
    
        $objActSheet = $objPHPExcel->getActiveSheet();
        
        // 水平居中（位置很重要，建议在最初始位置）
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        
        $objActSheet->setCellValue('A1', '账号');
        $objActSheet->setCellValue('B1', '密码');
        // 设置个表格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(16);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(36);
        
        // 垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        
        // 处理表数据
        foreach($data as $k=>$v){
            $k +=2;
            $objActSheet->setCellValue('A'.$k, $v['user_name']);    
            $objActSheet->setCellValue('B'.$k, $v['password2']);                  
                
            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(20);
            
        }
        
        $fileName = '账号密码表';
        $date = date("Y-m-d",time());
        $fileName .= "_{$date}.xlsx";

        $fileName = iconv("utf-8", "gb2312", $fileName);

        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=\"$fileName\"");
        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output'); //文件通过浏览器下载
        // END   
    }
    
    //导入身份信息
    public function impUser(){
        if (!empty($_FILES)) {            
            $info = $this->upload(['zip'],'/zips/',array('date', 'Y-m-d'));
            $zip_name='./Uploads'.$info['import']['savepath'].$info['import']['savename'];      //上传后的zip包
            
            vendor("Unzip.Unzip");
            $z = new \Unzip();            
            $z->unzip($zip_name,'./Uploads/unzipres/', true, false);                            //解压
                
////                exit;
                $file_name = './Uploads/unzipres/idinfo/info.xlsx';
                if(file_exists($file_name)){
                    $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
                    $objPHPExcel = $objReader->load($file_name,$encode='utf-8');
                    $sheet = $objPHPExcel->getSheet(0);                //dump($sheet);exit;
                    $highestRow = $sheet->getHighestRow(); // 取得总行数
    //                dump($highestRow);exit;
                    $ary=[];
                    if($highestRow > 3){
                        for($i=2;$i<=$highestRow;$i++)
                        {                       
                            $ary[]=['idname'=>trim($objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue()),'idcard'=>trim($objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue())];      //提取数据
                        } 
                    }
                    $bry = [];
                    foreach ($ary as $key=>$v){                                     //去除重复
                       if(in_array(strtolower($v['idcard']),$bry) || $v['idcard'] == null || $v['idname'] == null){
                           unset($ary[$key]);
                       }else{
                           $bry[] = strtolower($v['idcard']);
                       }
                    }
//                    dump($ary);
                    $admin_user = D('AdminUser');
                    if(!empty($ary)){
                        foreach ($ary as $key=>$value) {
                            if($value['idcard'] != null || !empty($value['idcard'])){
                                $result = $admin_user->find('identity_information',['idcard'=>$value['idcard']],'id');
                                if(!empty($result)){
                                    unset($ary[$key]);
                                }
                            }else{
                                unset($ary[$key]);
                            }
                        }
                        if(!empty($ary)){
                            $insert = $admin_user->insertAll('identity_information',$ary);                                            //写入数据
                        }
                     }else{
                        $idinfoMessage = 'idinfo.xlsx文件无有效内容!';
                    }
                }
                
                $dirName = './Uploads/unzipres/idinfo/idphoto';
                if(file_exists($dirName)){
                    $filenames = scandir($dirName);                //dump($filenames);exit;
                    if(count($filenames) > 2){
                        $idinfo = [];
                         foreach($filenames as $sub=>$file){
                            if($file != '.' && $file != '..'){
                                $file = substr($file, 0,-4);           //去除.jpg后缀
                                $idinfo[] = $file;
                            }
                         }

                         $idphotoDirs = scandir("./Public/images/idphoto");
                         foreach ($idinfo as $key => $value) {          
                             $data['idcard'] = $value;
                             $result = $admin_user->find('identity_information',$data);
                             if(!empty($result) && intval($result['isupload']) == 0){
                                 $admin_user->update('identity_information',$data,'isupload',1);
                                 $dirName = substr($value,0,2);
                                 if(!in_array($dirName, $idphotoDirs)){
                                      $z->create_dirs("./Public/images/idphoto/".$dirName."/");
                                     }
                                   rename("./Uploads/unzipres/idinfo/idphoto/".$value.".jpg", "./Public/images/idphoto/".$dirName."/".$value.".jpg"); 
                             }
                         }
                    } else {
                        $idphotoMessage = 'idphoto目录为空!';
                    }
                }
                  $this->delDirAndFile("./Uploads/unzipres/");
                  $this->delDirAndFile("./Uploads/zips/");
                  unset($ary);
                  unset($filenames);
                  unset($idinfo);
                 if($idinfoMessage || $idphotoMessage){
                     $this->error($idinfoMessage.'</br>'.$idphotoMessage);
                 } 
                 $this->error('导入成功！');
        }else
            {
                $this->error("请选择上传的文件");
            }            
    }
    
    //导出
    public function export(){
        if(!empty($_FILES)){
                $subName = $this->guid();
                $info = $this->upload(['xlsx'],'/xlsx/',$subName);

                $file_name='./Uploads'.$info['import']['savepath'].$info['import']['savename'];
                $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
                $objPHPExcel = $objReader->load($file_name,$encode='utf-8');
                $sheet = $objPHPExcel->getSheet(0);                //dump($sheet);exit;
                $highestRow = $sheet->getHighestRow(); // 取得总行数

                $userInfo = [];
                if($highestRow > 3){
                    for($i=2;$i<=$highestRow;$i++){   
                        $userInfo[] =['idname'=>trim($objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue()),'idcard'=>trim($objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue())];
                    } 
                }
//                dump($userInfo);
               if(empty($userInfo)){
                   $this->delDirAndFile("./Uploads/xlsx/".$subName."/");
                   $this->error('info.xlsx文件无有效内容!');
               } 
               
               $cry = [];
                    foreach ($userInfo as $key=>$v){                                     //去除重复
                       if((in_array(strtolower($v['idcard']),$cry) && !empty($v['idcard'])) || (in_array($v['idname'],$cry) && !empty($v['idname'])) ){
                           unset($userInfo[$key]);
                       }else{
                           $cry[] = strtolower($v['idcard']);
                           $cry[] = $v['idname'];
                       }
                    }
               
               $admin_user = D('AdminUser');
               $exportInfo = [];
//               dump($userInfo);
               foreach ($userInfo as $value) {
                    if(empty($value['idcard'])){
                      $nameResult = $admin_user->select('identity_information',['idname'=>$value['idname']],'','rand()',0,5);                    //   dump($nameResult);
                      if(!empty($nameResult)){
                          foreach ($nameResult as $info){
                               $exportInfo[] = $info;
                          }
                      }                    
                    }else{                     
                        $cardResult = $admin_user->find('identity_information',['idcard'=>$value['idcard']]);                      //dump($cardResult);
                       if(!empty($cardResult)){
                            $exportInfo[] = $cardResult; 
                       }
                    }
               }
//               dump($exportInfo);
        if(empty($exportInfo)){
            $this->delDirAndFile("./Uploads/xlsx/".$subName."/");
            $this->error('无匹配数据!');
        }       
               
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);  
        $objActSheet = $objPHPExcel->getActiveSheet();
        
        // 水平居中（位置很重要，建议在最初始位置）
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        
        $objActSheet->setCellValue('A1', '姓名');
        $objActSheet->setCellValue('B1', '身份证号码');
        $objActSheet->setCellValue('C1', '证件照状态');
        
        // 设置个表格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(16);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        
        // 垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        
        // 处理表数据
        $images = [];
        $order_goods = [];
        $user_id = session('user_id');
        $user_name = session('user_name');
        foreach($exportInfo as $k=>$v){
            $k +=2;
            $objActSheet->setCellValue('A'.$k, $v['idname']);    
            $objActSheet->setCellValue('B'.$k, ' '.$v['idcard']); 
            if(intval($v['isupload']) == 0){
                $objActSheet->setCellValue('C'.$k,'暂无证件照片!');
            }else{
                 $images[] = $v['idcard'];          
            }
            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(20); 
            if($user_id != 1 && $user_name != 'admin'){           
                $recordResult = $admin_user->find('record',['user_id'=>$user_id,'idcard'=>$v['idcard']]);
                if(empty($recordResult) && $v['idname'] != null && $v['idcard']!= null){
                    $record_id = $admin_user->insertOne('record',['user_id'=>$user_id,'idname'=>$v['idname'],'idcard'=>$v['idcard'],'num'=>1]);
                    $order_goods[] = $record_id;
                }else{
                    $admin_user->plus('record',['id'=>$recordResult['id']],'num',1);
                }
            }
        }
        if($user_id != 1 && $user_name != 'admin'){
            if(!empty($order_goods)){
                $order_sn = $this->get_order_sn();
                $total_price = count($order_goods)*3;
                $order_id = $admin_user->insertOne('order_info',['user_id'=>$user_id,'user_name'=>$user_name,'order_sn'=>$order_sn,'order_status'=>0,'total_price'=>$total_price,'addtime'=> time()]);
                foreach ($order_goods as $id) {
                    $admin_user->update('record',['id'=>$id],'order_id',$order_id);
                }
            }
        }
//        dump($images);
        $rand = $this->guid();
        $dir = './Uploads/temp/'. $rand.'/';
        $this->create_dirs($dir);
        if(!empty($images)){
            foreach ($images as $img) {
                copy("./Public/images/idphoto/". substr($img,0,2)."/".$img.".jpg", $dir.$img.".jpg");                                           //复制图片到指定目录准备打包
            }
        }
//        dump($images);     
        
        $fileName = '身份信息表';
        $date = date("Y-m-d",time());
        $fileName .= "_{$date}.xlsx";
        $fileName = iconv("utf-8", "gb2312", $fileName);;
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);

        
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');        //dump($objWriter);
        $objWriter->save($dir.$fileName);                       //生成的Execl表格移動到指定目錄
        $this->delDirAndFile("./Uploads/xlsx/".$subName."/");
        
                  vendor('FileToZip.FileToZip');
                  $handler = opendir($dir);    //$cur_file 文件所在目录
                  $download_file = array();
                  $i = 0;
                  while( ($filename = readdir($handler)) !== false ) {
                      if($filename != '.' && $filename != '..') {
                          $download_file[$i++] = $filename;
                      }
                  }
                  closedir($handler);
                  $scandir=new \traverseDir($dir,'./Uploads/');    //$save_path zip包文件目录
                  $scandir->tozip($download_file,$dir);
        } else {
            $this->error('请选择上传文件!');
        }   
    }
    
    //按数量导出
    public function numExport(){
        $num = (int)I('num');        //dump($num);
        if(empty($num)) return FALSE;
        
        ini_set('memory_limit','3072M');
        set_time_limit(0);
        
        $admin_user = D('AdminUser');
        $result = $admin_user->select('identity_information','','','id asc',0,$num);        //dump($result);
        
        $objPHPExcel = new \PHPExcel();
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);  
        $objActSheet = $objPHPExcel->getActiveSheet();
        
        // 水平居中（位置很重要，建议在最初始位置）
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);   
        $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        
        $objActSheet->setCellValue('A1', '姓名');
        $objActSheet->setCellValue('B1', '身份证号码');
        $objActSheet->setCellValue('C1', '证件照状态');
        $objActSheet->setCellValue('D1', '是否有图');
        
        // 设置个表格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(16);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        
        // 垂直居中
        $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        
        foreach($result as $k=>$v){
            $k +=2;
            $objActSheet->setCellValue('A'.$k, $v['idname']);    
            $objActSheet->setCellValue('B'.$k, ' '.$v['idcard']); 
            $objActSheet->setCellValue('C'.$k, '成功'); 
            if(intval($v['isupload']) == 0){
                $objActSheet->setCellValue('D'.$k,'无图');
            }else{
                $objActSheet->setCellValue('D'.$k,'有图');
                $images[] = $v['idcard'];          
            }
            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(20); 
        }
        
        $rand = $this->guid();
        $dir = './Uploads/temp/'. $rand.'/';
        $this->create_dirs($dir);
        if(!empty($images)){
            foreach ($images as $img) {
                copy("./Public/images/idphoto/". substr($img,0,2)."/".$img.".jpg", $dir.$img.".jpg");                                           //复制图片到指定目录准备打包
            }
        }
//        dump($images);     
        
        $fileName = '身份信息表';
        $date = date("Y-m-d",time());
        $fileName .= "_{$date}.xlsx";
        $fileName = iconv("utf-8", "gb2312", $fileName);;
        //设置活动单指数到第一个表,所以Excel打开这是第一个表
        $objPHPExcel->setActiveSheetIndex(0);

        
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');        //dump($objWriter);
        $objWriter->save($dir.$fileName);                       //生成的Execl表格移動到指定目錄
        
                  vendor('FileToZip.FileToZip');
                  $handler = opendir($dir);    //$cur_file 文件所在目录
                  $download_file = array();
                  $i = 0;
                  while( ($filename = readdir($handler)) !== false ) {
                      if($filename != '.' && $filename != '..') {
                          $download_file[$i++] = $filename;
                      }
                  }
                  closedir($handler);
                  $scandir=new \traverseDir($dir,'./Uploads/');    //$save_path zip包文件目录
                  $scandir->tozip($download_file,$dir);
    }
}
